IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetAuthenGroup')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetAuthenGroup
GO

/****** Object:  StoredProcedure [dbo].[USP_GetAuthenGroup]    Script Date: 10/27/2010 01:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 30/10/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetAuthenGroup]
	-- Add the parameters for the stored procedure here
	@staff_code int,
	@group_code int,
	@main_system_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	declare @department_code as int
	declare @auth_department as table(auth_department_code int, department_code int, sub_menu_code int)
	declare @auth_group as table(auth_group_code int, group_code int, auth_dep_code int)

	select @department_code = department_code from group_user where group_user_code = @group_code
	
	insert into @auth_department
	select auth_department_code, department_code, sub_menu_code from stp_auth_department where department_code = @department_code
	
	insert into @auth_group
	select auth_group_code, group_code, auth_dep_code from stp_auth_group where group_code = @group_code

	/*************************************************/
	/*  Get list of menu from @auth_sub_menu_table   */
	/*                                               */
	/*                                               */
	/*************************************************/
	select distinct main.main_system_code,
			main.main_system_name,
			menu.menu_system_code,
			menu.menu_system_name,
			sub_menu.sub_menu_code,
			sub_menu.sub_menu_name,
			sub_menu.sub_menu_url,
			sub_menu.type_code,
			ISNULL(auth.auth_department_code, 0) auth_department_code,
			case ISNULL(auth.auth_department_code, 0) when 0 then 'false' else 'true' end has_authen_department,
			ISNULL(auth_g.auth_group_code , 0) auth_group_code,
			case ISNULL(auth_g.auth_group_code, 0) when 0 then 'false' else 'true' end has_authen
	from @auth_department auth
	left join @auth_group auth_g on auth.auth_department_code = auth_g.auth_dep_code 
	inner join stp_sub_menu sub_menu on sub_menu.sub_menu_code = auth.sub_menu_code 
	inner join stp_menu_system menu on menu.menu_system_code = sub_menu.menu_system_code 
	inner join stp_main_system main on main.main_system_code = menu.main_system_code
	where menu.menu_system_status = 1
			and main.main_system_type > 0
			and @main_system_code in (0, main.main_system_code)
	order by main.main_system_code, menu.menu_system_code, sub_menu.type_code


END

GO
